﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace wzSqlAdmin.Web
{
    public class SqlData
    {
        private SqlConnection _Conn;
        public void ApriConn(Dictionary<string, string> parametri)
        {
            HttpContext http = HttpContext.Current;
            _Conn = new SqlConnection();

            _Conn.ConnectionString = "server=" + parametri["Server"] + ";";
            if (parametri["DB"] != "")
                _Conn.ConnectionString += "database=" + parametri["DB"] + ";";

            if (bool.Parse(parametri["trusted"]))
            {
                _Conn.ConnectionString += "Integrated Security = SSPI;";
            }
            else
            {
                _Conn.ConnectionString += "UID=" + parametri["UID"] + ";PWD=" + parametri["Pwd"] + ";";
            }
            _Conn.Open();
        }
        public void ChiudiConn()
        {

            _Conn.Close();
        }
        public SortedDictionary<string, string> ListDataBases(Dictionary<string, string> parametri)
        {

            ApriConn(parametri);

            DataTable db = _Conn.GetSchema("Databases");
            SortedDictionary<string, string> Lista = new SortedDictionary<string, string>();

            foreach (DataRow r in db.Rows)
            {
                Lista.Add(r["database_name"].ToString(), "database");
            }
            ChiudiConn();

            return Lista;
        }
        public SortedDictionary<string, string> ListObjects(Dictionary<string, string> parametri)
        {


            ApriConn(parametri);
            SortedDictionary<string, string> Lista = new SortedDictionary<string, string>();

            DataTable procs = _Conn.GetSchema("Procedures");
            string[] restr = new string[4];
            restr[0] = null;
            restr[1] = null;
            restr[2] = null;
            restr[3] = "BASE TABLE";
            DataTable tables = _Conn.GetSchema("Tables");//,restr);
            DataTable users = _Conn.GetSchema("Users");
            DataTable views = _Conn.GetSchema("Views");
            foreach (DataRow r in tables.Rows)
            {
                Lista.Add(r["table_name"].ToString(), r["table_type"].ToString().ToLower());
            }
            //foreach (DataRow r in views.Rows)
            //{
            //    Lista.Add(parametri["DB"] + "-" + r["table_name"].ToString(), "view");
            //}
            foreach (DataRow r in users.Rows)
            {
                Lista.Add(r["user_name"].ToString(), "user");
            }
            foreach (DataRow r in procs.Rows)
            {
                Lista.Add(r["routine_name"].ToString(), r["routine_type"].ToString().ToLower());
            }



            return Lista;
        }
        public DataSet GetDataSet(Dictionary<string, string> parametri, string SQL, int PageNumber, int PageSize,out int TotRecords)
        {
            DataSet ds;

            try
            {
                ApriConn(parametri);
                // Get RowCount (TotRecords)
                SqlCommand cmdt = new SqlCommand();
                cmdt.CommandText = "select count(*) from (" + SQL +") T ";
                cmdt.Connection = _Conn;
                TotRecords = (int)cmdt.ExecuteScalar();

                
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = SQL;
                cmd.Connection = _Conn;
                

                SqlDataAdapter adapter = new SqlDataAdapter();

                adapter.SelectCommand = cmd;

                if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                {
                    long RowCount = PageNumber * PageSize;
                    SqlCommand cmds = new SqlCommand("SET ROWCOUNT " + RowCount.ToString() + " SET NO_BROWSETABLE ON");
                    cmds.Connection = _Conn;
                    cmds.ExecuteNonQuery();
                }
                ds = new DataSet();
                adapter.Fill(ds, (PageNumber - 1) * PageSize, PageSize, "Data");
                adapter.FillSchema(ds, SchemaType.Mapped, "DataSchema");
                if (PageSize > 0) // Reset Rowcount back to 0
                {
                    SqlCommand cmds = new SqlCommand("SET ROWCOUNT 0 SET NO_BROWSETABLE OFF");

                    cmds.Connection = _Conn;
                    cmds.ExecuteNonQuery();
                }
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables["Data"];
                    DataTable schema = ds.Tables["DataSchema"];
                    data.Merge(schema);
                    ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                ChiudiConn();
            }
            return ds;
        }
        public DataSet GetSchema(Dictionary<string, string> parametri, string Table, schematype Schema, int PageNumber, int PageSize)
        {
            DataSet ds;

            try
            {
                ApriConn(parametri);
                ds = new DataSet();

                SqlCommand cmd = new SqlCommand();

                SqlDataAdapter da = new SqlDataAdapter();

                string sql;
                cmd = new SqlCommand();
                switch (Schema)
                {

                    case schematype.UserInfo:
                        sql = "select " +
                        " [Login Type]=" +
                        " cast(case sp.type" +
                        " when 'u' then 'WIN'" +
                        " when 's' then 'SQL'" +
                        " when 'g' then 'GRP'" +
                        " end as varchar)," +
                        " convert(char(45),sp.name) as srvLogin, " +
                        " convert(char(45),sp2.name) as srvRole," +
                        " convert(char(25),dbp.name) as dbUser," +
                        " convert(char(25),dbp2.name) as dbRole" +
                        " from " +
                        " sys.server_principals as sp join" +
                        " sys.database_principals as dbp on sp.sid=dbp.sid join" +
                        " sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join " +
                        " sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join " +
                        " sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join" +
                        " sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id" +
                        " where dbp.name='" + Table + "'";
                        break;

                    default:
                        sql = "sp_help  '" + Table + "'";
                        break;

                }
                cmd.CommandText = sql;
                // Restituisce:
                // Tabella 1: Nome Tabella
                // 2: Colonne
                // 3: Identity
                // 4: Guid
                // 5: FileGroup
                // 6: Indici
                // 7: Vincoli
                // 8: Riferimenti foreign key
                // 9: Riferimenti view

                cmd.Connection = _Conn;
                da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, Table);


                System.Type varchar = System.Type.GetType("System.String");
                System.Type bl = System.Type.GetType("System.Boolean");
                System.Type lg = System.Type.GetType("System.Double");
                System.Type it = System.Type.GetType("System.Int32");

            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                ChiudiConn();
            }

            return ds;

        }
 
    }
}